package amalgame.data;

import java.sql.SQLException;
import java.util.List;

import amalgame.entity.db.Categorie;
import amalgame.entity.db.Customer;
import amalgame.entity.db.OrderProduct;
import amalgame.entity.db.Orders;
import amalgame.entity.db.Product;
import amalgame.entity.db.Stock;
import android.content.Context;

import com.j256.ormlite.dao.Dao.CreateOrUpdateStatus;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.PreparedQuery;
import com.j256.ormlite.stmt.QueryBuilder;

public class DatabaseManager {
	static private DatabaseManager instance;

	static public void init(Context ctx) {
		if (null == instance) {
			instance = new DatabaseManager(ctx);
		}
	}

	static public DatabaseManager getInstance() {
		return instance;
	}

	private DataSQLiteHelper helper;

	private DatabaseManager(Context ctx) {
		helper = new DataSQLiteHelper(ctx);
	}

	private DataSQLiteHelper getHelper() {
		return helper;
	}

	// #######################################################################################
	// STOCK
	// #######################################################################################

	public boolean updateStock(Stock p) {
		boolean ret = false;
		int retInt = 0;
		CreateOrUpdateStatus createOrUpdateStatus;
		try {
			createOrUpdateStatus = getHelper().getStockDao().createOrUpdate(p);
			if (createOrUpdateStatus.isUpdated()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			ret = false;
		}
		return ret;
	}

	public void addStock(Stock p) {
		try {
			getHelper().getStockDao().create(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<Stock> getAllStock() {
		List<Stock> stockList = null;
		try {
			stockList = getHelper().getStockDao().queryForAll();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return stockList;
	}

	public int getQuantityByProductId(int product_id) {
		int quantity = 0;

		List<Stock> stockList = null;
		try {
			stockList = getHelper().getStockDao().queryForEq("product_id",
					product_id);
			for (Stock stock : stockList) {
				quantity += stock.getQuantity();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return quantity;
	}

	public List<Stock> getAllStockByProductID(int product_id) {
		List<Stock> stockList = null;
		try {
			stockList = getHelper().getStockDao().queryForEq("product_id",
					product_id);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return stockList;
	}

	// #######################################################################################
	// customer
	// #######################################################################################
	public List<Customer> getAllCustomer() {
		List<Customer> customerList = null;
		try {
			customerList = getHelper().getCustomerDao().queryForAll();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return customerList;
	}

	public void addCustomer(Customer c) {
		try {
			getHelper().getCustomerDao().create(c);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/*
	 * public int delete(City city) { // first delete the clients that match the
	 * city's id DeleteBuilder db = clientDao.deleteBuilder();
	 * db.where().eq("city_id", city.getId()); cityDao.delete(db.prepare()); //
	 * then call the super to delete the city return super.delete(city); }
	 */

	public void deleteCustomerByID(int iD) {
		try {
			// borramos las ordenes que estan relacionadas con el customer id
			DeleteBuilder db = getHelper().getOrdersDao().deleteBuilder();
			db.where().eq("customer_id", iD);
			getHelper().getCustomerDao().delete(db.prepare());
			// borramos el Customer en cuesti�n
			getHelper().getCustomerDao().deleteById(iD);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public Customer getCustomerByID(int customerId) {
		Customer p = null;

		try {
			p = getHelper().getCustomerDao().queryForId(customerId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return p;
	}

	public boolean updateCustomer(Customer p) {
		boolean ret = false;
		try {
			ret = getHelper().updateCustomer(p);
		} catch (Exception e) {
			e.printStackTrace();
			ret = false;
		}
		return ret;

	}

	// #######################################################################################
	// PRODUCT
	// #######################################################################################

	public int getIdProductFromDB(String name) {
		QueryBuilder<Product, Integer> queryBuilder = getHelper()
				.getProductDao().queryBuilder();
		try {
			queryBuilder.where().eq("name", name);
			PreparedQuery<Product> preparedQuery = queryBuilder.prepare();
			// query for all accounts that have "qwerty" as a password
			List<Product> productList = getHelper().getProductDao().query(
					preparedQuery);
			return productList.get(0).getProductId();
		} catch (java.sql.SQLException e) {
			e.printStackTrace();
			return -1;
		}

	}

	public List<Product> getAllproduct() {
		List<Product> list = null;
		try {
			list = getHelper().getProductDao().queryForAll();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;
	}

	// agregado
	public void deleteProductByID(int iD) {
		try {
			getHelper().getProductDao().deleteById(iD);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void addProduct(Product p) {
		try {
			getHelper().getProductDao().create(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean updateProduct(Product p) {
		boolean ret = false;
		int retInt = 0;
		CreateOrUpdateStatus createOrUpdateStatus;
		try {
			createOrUpdateStatus = getHelper().getProductDao()
					.createOrUpdate(p);
			if (createOrUpdateStatus.isUpdated()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			ret = false;
		}
		return ret;
	}

	public Product getProductByID(int productId) {
		Product p = null;

		try {
			p = getHelper().getProductDao().queryForId(productId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return p;
	}

	// #######################################################################################
	// CATEGORIE
	// #######################################################################################
	public void addcategorie(Categorie c) {
		try {
			getHelper().getCategorieDao().create(c);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Categorie getItemByName(String name) {
		try {
			List<Categorie> list = null;
			list = getHelper().getCategorieDao().queryForEq("name", name);
			return list.get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Categorie> getAllCategories() {
		List<Categorie> list = null;
		try {
			list = getHelper().getCategorieDao().queryForAll();
		} catch (Exception e) {
			e.printStackTrace();

		}

		return list;
	}

	public void deleteCategoryById(int ID) {
		try {
			getHelper().getCategorieDao().deleteById(ID);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// #######################################################################################
	// CUSTOMER ORDER
	// #######################################################################################

	public void addOrders(Orders p) {
		try {
			getHelper().getOrdersDao().create(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<Orders> getAllOrders() {
		List<Orders> list = null;
		try {
			list = getHelper().getOrdersDao().queryForAll();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;
	}

	// Peter
	public void deleteOrdersById(int ID) {
		try {
			getHelper().getOrdersDao().deleteById(ID);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int deleteOrders(int ID) {
		int ret = -1;
		try {
			ret = getHelper().getOrdersDao().deleteById(ID);
		} catch (Exception e) {
			e.printStackTrace();
			ret = -1;
		}
		return ret;
	}

	// #######################################################################################
	// orders product
	// #######################################################################################

	public boolean updateOrder(Orders p) {
		boolean ret = false;
		CreateOrUpdateStatus createOrUpdateStatus;
		try {
			createOrUpdateStatus = getHelper().getOrdersDao().createOrUpdate(p);
			if (createOrUpdateStatus.isUpdated()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			ret = false;
		}
		return ret;
	}

	public void g(int ID) {
		try {
			getHelper().getOrdersDao().deleteById(ID);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void addOrderProduct(OrderProduct p) {
		try {
			getHelper().getOrderProductDao().create(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<OrderProduct> getAllOrderProduct() {
		List<OrderProduct> list = null;
		try {
			list = getHelper().getOrderProductDao().queryForAll();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;
	}

	public List<OrderProduct> getAllOrderProductByOrderID(int idOrder) {
		try {
			List<OrderProduct> list = null;
			list = getHelper().getOrderProductDao().queryForEq("order_id",
					idOrder);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	public boolean updateOrderProductQuantityByyOrderId(
			OrderProduct orderProduct_edit) {

		boolean ret = false;
		int retInt = 0;
		CreateOrUpdateStatus createOrUpdateStatus;
		try {
			createOrUpdateStatus = getHelper().getOrderProductDao()
					.createOrUpdate(orderProduct_edit);
			if (createOrUpdateStatus.isUpdated()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			ret = false;
		}
		return ret;

	}

	public int deleteOrderProduct(int ID) {
		int ret = -1;
		try {
			ret = getHelper().getOrderProductDao().deleteById(ID);
		} catch (Exception e) {
			e.printStackTrace();
			ret = -1;
		}
		return ret;
	}

	// #######################################################################################
	// orders
	// #######################################################################################

	/*
	 * public List<WishList> getAllWishLists() { List<WishList> wishLists =
	 * null; try { wishLists = getHelper().getWishListDao().queryForAll(); }
	 * catch (SQLException e) { e.printStackTrace(); } return wishLists; }
	 */
}
